﻿using System;
using System.Collections.Generic; 
using System.Text;
using System.Data;
using Runda.EShop.Common.Utility;
using System.Data.Common;

namespace Runda.EShop.Dal
{
    public partial class SysMessageDal
    {
        public DataTable GetUserMessage(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} * FROM Sys_Message WHERE Deleted = 0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);
            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
             
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public void ReadMessage(Guid ID)
        {
            string sql = "UPDATE Sys_Message SET IsRead=1,ReadAt=GetDate() WHERE ID = @ID";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ID", DbType.Guid, ID);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public void SendMessageToUser(Guid RoleID,string Title,string Content)
        {
            string sql = @"INSERT INTO Sys_Message (ID,Title,Content,ObjectType,ObjectID,MsgType,IsRead,ReadAt,ReadBy,CreatedAt,Deleted) 
            SELECT NEWID(),@Title,@Content,@ObjectType,UserID,0,0,NULL,NULL,GETDATE(),0 FROM Sys_UserRoleRelation WHERE RoleID =@RoleID";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@Title", DbType.String, Title);
            CurrentDatabase.AddInParameter(dbcommand, "@Content", DbType.String, Content);
            CurrentDatabase.AddInParameter(dbcommand, "@ObjectType", DbType.Int32, (int)EnumHelper.UserType.Administrator);
            CurrentDatabase.AddInParameter(dbcommand, "@RoleID", DbType.Guid, RoleID);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public int Count(List<SqlDbParameter> parms)
        {
            string sql = "SELECT {0} COUNT(1) FROM Sys_Message WHERE Deleted=0 {1}";
            sql = SqlDbParameter.BuildSqlString(sql, parms);
            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            return (int)CurrentDatabase.ExecuteScalar(dbCommand);
        }
    }
}
